﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace dzs.Web.Admin.materialinfo
{
    public partial class importData : Web.Admin.BasePage
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnimport_Click(object sender, EventArgs e)
        {
            string filename = string.Empty;
            try
            {
                filename = UpLoadXls(FileExcel);//上传XLS文件
                ImportXlsToData(filename);//将XLS文件的数据导入数据库               
                if (filename != string.Empty && System.IO.File.Exists(filename))
                {
                    System.IO.File.Delete(filename);//删除上传的XLS文件
                }               

                Maticsoft.Common.MessageBox.Show(this, "导入成功！");
            }
            catch (Exception ex)
            {
                LblMessage.Text = ex.Message;
            }

        }
        private void ImportXlsToData(string fileName)
        {
            try
            {
                if (fileName == string.Empty)
                {
                    throw new ArgumentNullException("Excel文件上传失败！");
                }
                string oleDBConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + fileName + "; Extended Properties='Excel 8.0;IMEX=1'";
                OleDbConnection oleDBConn = null;
                OleDbDataAdapter oleAdMaster = null;
                DataTable m_tableName = new DataTable();
                DataSet ds = new DataSet();
                oleDBConn = new OleDbConnection(oleDBConnString);
                oleDBConn.Open();
                m_tableName = oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (m_tableName != null && m_tableName.Rows.Count > 0)
                {
                    m_tableName.TableName = "Sheet1$";
                }
                string sqlMaster;
                sqlMaster = " SELECT *  FROM [" + m_tableName.TableName + "]";
                oleAdMaster = new OleDbDataAdapter(sqlMaster, oleDBConn);
                oleAdMaster.Fill(ds, "m_tableName");
                oleAdMaster.Dispose();
                // oleDBConn.Close();
                oleDBConn.Dispose();
                AddDatasetToSQL(ds, 10);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        private string UpLoadXls(System.Web.UI.HtmlControls.HtmlInputFile inputfile)
        {
            string orifilename = string.Empty;
            string uploadfilepath = string.Empty;
            string modifyfilename = string.Empty;
            string fileExtend = "";//文件扩展名
            int fileSize = 0;//文件大小
            try
            {
                if (inputfile.Value != string.Empty)
                {
                    //得到文件的大小
                    fileSize = inputfile.PostedFile.ContentLength;
                    if (fileSize == 0)
                    {
                        throw new Exception("导入的Excel文件大小为0，请检查是否正确！");
                    }
                    //得到扩展名
                    fileExtend = inputfile.Value.Substring(inputfile.Value.LastIndexOf(".") + 1);
                    if (fileExtend.ToLower() != "xls")
                    {
                        throw new Exception("你选择的文件格式不正确，只能导入EXCEL文件！");
                    }
                    //路径
                    uploadfilepath = Server.MapPath("~/upload/");
                    //新文件名
                    modifyfilename = System.Guid.NewGuid().ToString();
                    //modifyfilename = inputfile.PostedFile.FileName;
                    modifyfilename += "." + inputfile.Value.Substring(inputfile.Value.LastIndexOf(".") + 1);
                    //判断是否有该目录
                    System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(uploadfilepath);
                    if (!dir.Exists)
                    {
                        dir.Create();
                    }
                    if (!uploadfilepath.EndsWith("\\"))
                    orifilename = uploadfilepath + "\\" + modifyfilename;
                    else
                        orifilename = uploadfilepath +  modifyfilename;
                    //如果存在,删除文件
                    if (File.Exists(orifilename))
                    {
                        File.Delete(orifilename);
                    }
                    // 上传文件
                    inputfile.PostedFile.SaveAs(orifilename);
                }
                else
                {
                    throw new Exception("请选择要导入的Excel文件!");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return orifilename;
        }
        private bool AddDatasetToSQL(DataSet pds, int Cols)
        {
            dzs.BLL.materialinfo ms = new BLL.materialinfo();
            dzs.Model.materialinfo model;         

            int ic, ir;
            ic = pds.Tables[0].Columns.Count;
            if (pds.Tables[0].Columns.Count < Cols)
            {
                throw new Exception("导入Excel格式错误！Excel只有" + ic.ToString() + "列");
            }
            //供应商名称	项目编号	项目名称	物料名称	申请数量	单位	物料编码	规格型号	供应商联系人	联系电话
            decimal unitprice=0;
            ir = pds.Tables[0].Rows.Count;
            if (pds != null && pds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < pds.Tables[0].Rows.Count; i++)
                {

                    model = new Model.materialinfo();
                    model.proid = pds.Tables[0].Rows[i][0].ToString().Trim();
                    if (model.proid.Trim() != "" && model.proid.Trim() != "项目编号")
                    {
                        //model.proid = pds.Tables[0].Rows[i][0].ToString().Trim();
                        model.supplier = pds.Tables[0].Rows[i][1].ToString().Trim();
                        model.projectname = pds.Tables[0].Rows[i][2].ToString().Trim();
                        model.material = pds.Tables[0].Rows[i][3].ToString().Trim();
                        decimal.TryParse(pds.Tables[0].Rows[i][4].ToString().Trim(), out unitprice);
                        model.nums = unitprice;
                        unitprice = 0;                        
                        model.unit = pds.Tables[0].Rows[i][5].ToString().Trim();
                        model.mbm = pds.Tables[0].Rows[i][6].ToString().Trim();
                        model.mmodel = pds.Tables[0].Rows[i][7].ToString().Trim();
                        model.suppliername = pds.Tables[0].Rows[i][8].ToString().Trim();
                        model.supplierphone = pds.Tables[0].Rows[i][9].ToString().Trim();
                        decimal.TryParse( pds.Tables[0].Rows[i][10].ToString().Trim(),out unitprice);
                        model.unitPrice = unitprice;
                        model.mstatus = 0;
                        model.uid = 0;
                        model.mtype = 0;
                        model.cguid = getid();
                        ms.Add(model);
                    }
                }
            }
            else
            {
                throw new Exception("导入数据为空！");
            }
            return true;
        }   

    }
}